<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width">
<meta name="theme-color" content="#222"><meta name="generator" content="Hexo 6.3.0">

  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">



<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.1/css/all.min.css" integrity="sha256-Z1K5uhUaJXA7Ll0XrZ/0JhX4lAtZFpT6jkKrEDT0drU=" crossorigin="anonymous">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/animate.css/3.1.1/animate.min.css" integrity="sha256-PR7ttpcvz8qrF57fur/yAx1qXMFJeJFiA6pSzWi0OIE=" crossorigin="anonymous">

<script class="next-config" data-name="main" type="application/json">{"hostname":"example.com","root":"/","images":"/images","scheme":"Muse","darkmode":false,"version":"8.14.1","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12},"copycode":{"enable":false,"style":null},"bookmark":{"enable":false,"color":"#222","save":"auto"},"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"stickytabs":false,"motion":{"enable":true,"async":false,"transition":{"menu_item":"fadeInDown","post_block":"fadeIn","post_header":"fadeInDown","post_body":"fadeInDown","coll_header":"fadeInLeft","sidebar":"fadeInUp"}},"prism":false,"i18n":{"placeholder":"搜索...","empty":"没有找到任何搜索结果：${query}","hits_time":"找到 ${hits} 个搜索结果（用时 ${time} 毫秒）","hits":"找到 ${hits} 个搜索结果"},"path":"/search.xml","localsearch":{"enable":true,"trigger":"auto","top_n_per_article":-1,"unescape":false,"preload":false}}</script><script src="/js/config.js"></script>

    <meta name="description" content="索引的数据结构B树、B+树 关于 B树、B+树本身的介绍，不是本文的重点，之后也许会专门写一篇文章详细介绍，  这里直接就贴出wiki的链接了。 B树 B+树  为什么不使用普通的平衡二叉树（如RB树、AVL树）？ MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO 操作非常耗时，所以我们优化的重点就是尽量减少磁盘 IO 操作。访问二叉树的每个节点就会">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL系列——索引">
<meta property="og:url" content="http://example.com/2021/10/27/MySQL%E7%B3%BB%E5%88%97%E2%80%94%E2%80%94%E7%B4%A2%E5%BC%95/index.html">
<meta property="og:site_name" content="JsyBlog">
<meta property="og:description" content="索引的数据结构B树、B+树 关于 B树、B+树本身的介绍，不是本文的重点，之后也许会专门写一篇文章详细介绍，  这里直接就贴出wiki的链接了。 B树 B+树  为什么不使用普通的平衡二叉树（如RB树、AVL树）？ MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO 操作非常耗时，所以我们优化的重点就是尽量减少磁盘 IO 操作。访问二叉树的每个节点就会">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="http://example.com/2021/10/27/MySQL%E7%B3%BB%E5%88%97%E2%80%94%E2%80%94%E7%B4%A2%E5%BC%95/btree.png">
<meta property="og:image" content="http://example.com/2021/10/27/MySQL%E7%B3%BB%E5%88%97%E2%80%94%E2%80%94%E7%B4%A2%E5%BC%95/b+tree.png">
<meta property="og:image" content="http://example.com/2021/10/27/MySQL%E7%B3%BB%E5%88%97%E2%80%94%E2%80%94%E7%B4%A2%E5%BC%95/myisam.png">
<meta property="og:image" content="http://example.com/2021/10/27/MySQL%E7%B3%BB%E5%88%97%E2%80%94%E2%80%94%E7%B4%A2%E5%BC%95/innodb1.png">
<meta property="og:image" content="http://example.com/2021/10/27/MySQL%E7%B3%BB%E5%88%97%E2%80%94%E2%80%94%E7%B4%A2%E5%BC%95/innodb2.png">
<meta property="og:image" content="http://example.com/2021/10/27/MySQL%E7%B3%BB%E5%88%97%E2%80%94%E2%80%94%E7%B4%A2%E5%BC%95/rolltable.png">
<meta property="article:published_time" content="2021-10-27T06:08:19.000Z">
<meta property="article:modified_time" content="2021-10-27T08:30:02.390Z">
<meta property="article:author" content="SongyangJi">
<meta property="article:tag" content="MySQL">
<meta property="article:tag" content="索引">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="http://example.com/2021/10/27/MySQL%E7%B3%BB%E5%88%97%E2%80%94%E2%80%94%E7%B4%A2%E5%BC%95/btree.png">


<link rel="canonical" href="http://example.com/2021/10/27/MySQL%E7%B3%BB%E5%88%97%E2%80%94%E2%80%94%E7%B4%A2%E5%BC%95/">



<script class="next-config" data-name="page" type="application/json">{"sidebar":"","isHome":false,"isPost":true,"lang":"zh-CN","comments":true,"permalink":"http://example.com/2021/10/27/MySQL%E7%B3%BB%E5%88%97%E2%80%94%E2%80%94%E7%B4%A2%E5%BC%95/","path":"2021/10/27/MySQL系列——索引/","title":"MySQL系列——索引"}</script>

<script class="next-config" data-name="calendar" type="application/json">""</script>
<title>MySQL系列——索引 | JsyBlog</title>
  








  <noscript>
    <link rel="stylesheet" href="/css/noscript.css">
  </noscript>
</head>

<body itemscope itemtype="http://schema.org/WebPage" class="use-motion">
  <div class="headband"></div>

  <main class="main">
    <div class="column">
      <header class="header" itemscope itemtype="http://schema.org/WPHeader"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏" role="button">
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <i class="logo-line"></i>
      <p class="site-title">JsyBlog</p>
      <i class="logo-line"></i>
    </a>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger" aria-label="搜索" role="button">
        <i class="fa fa-search fa-fw fa-lg"></i>
    </div>
  </div>
</div>



<nav class="site-nav">
  <ul class="main-menu menu"><li class="menu-item menu-item-home"><a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a></li><li class="menu-item menu-item-tags"><a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签</a></li><li class="menu-item menu-item-categories"><a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类</a></li><li class="menu-item menu-item-archives"><a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档</a></li>
      <li class="menu-item menu-item-search">
        <a role="button" class="popup-trigger"><i class="fa fa-search fa-fw"></i>搜索
        </a>
      </li>
  </ul>
</nav>



  <div class="search-pop-overlay">
    <div class="popup search-popup"><div class="search-header">
  <span class="search-icon">
    <i class="fa fa-search"></i>
  </span>
  <div class="search-input-container">
    <input autocomplete="off" autocapitalize="off" maxlength="80"
           placeholder="搜索..." spellcheck="false"
           type="search" class="search-input">
  </div>
  <span class="popup-btn-close" role="button">
    <i class="fa fa-times-circle"></i>
  </span>
</div>
<div class="search-result-container no-result">
  <div class="search-result-icon">
    <i class="fa fa-spinner fa-pulse fa-5x"></i>
  </div>
</div>

    </div>
  </div>

</header>
        
  
  <aside class="sidebar">

    <div class="sidebar-inner sidebar-nav-active sidebar-toc-active">
      <ul class="sidebar-nav">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <div class="sidebar-panel-container">
        <!--noindex-->
        <div class="post-toc-wrap sidebar-panel">
            <div class="post-toc animated"><ol class="nav"><li class="nav-item nav-level-1"><a class="nav-link" href="#%E7%B4%A2%E5%BC%95%E7%9A%84%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84"><span class="nav-number">1.</span> <span class="nav-text">索引的数据结构</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#B%E6%A0%91%E3%80%81B-%E6%A0%91"><span class="nav-number">1.1.</span> <span class="nav-text">B树、B+树</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#MyISAM%E5%BC%95%E6%93%8E%E4%B8%AD%E7%9A%84%E7%B4%A2%E5%BC%95"><span class="nav-number">1.2.</span> <span class="nav-text">MyISAM引擎中的索引</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#InnoDB%E5%BC%95%E6%93%8E%E4%B8%AD%E7%9A%84%E7%B4%A2%E5%BC%95"><span class="nav-number">1.3.</span> <span class="nav-text">InnoDB引擎中的索引</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%B8%BB%E9%94%AE%E7%B4%A2%E5%BC%95%EF%BC%88%E8%81%9A%E7%B0%87%E7%B4%A2%E5%BC%95%EF%BC%89"><span class="nav-number">1.3.1.</span> <span class="nav-text">主键索引（聚簇索引）</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%BA%8C%E7%BA%A7%E7%B4%A2%E5%BC%95%EF%BC%88%E8%BE%85%E5%8A%A9%E7%B4%A2%E5%BC%95%EF%BC%89"><span class="nav-number">1.3.2.</span> <span class="nav-text">二级索引（辅助索引）</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E8%81%9A%E7%B0%87%E7%B4%A2%E5%BC%95-vs-%E9%9D%9E%E8%81%9A%E7%B0%87%E7%B4%A2%E5%BC%95"><span class="nav-number">1.4.</span> <span class="nav-text">聚簇索引 vs 非聚簇索引</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%BC%98%E5%8A%BF"><span class="nav-number">1.4.1.</span> <span class="nav-text">优势</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%8A%A3%E5%8A%BF"><span class="nav-number">1.4.2.</span> <span class="nav-text">劣势</span></a></li></ol></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E4%B8%80%E4%BA%9B%E6%B3%A8%E6%84%8F%E7%82%B9"><span class="nav-number">2.</span> <span class="nav-text">使用索引的一些注意点</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%9C%80%E5%B7%A6%E5%8C%B9%E9%85%8D%E5%8E%9F%E5%88%99"><span class="nav-number">2.1.</span> <span class="nav-text">最左匹配原则</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E8%A6%86%E7%9B%96%E7%B4%A2%E5%BC%95"><span class="nav-number">2.2.</span> <span class="nav-text">覆盖索引</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%90%88%E7%90%86%E5%BB%BA%E7%AB%8B%E8%81%94%E5%90%88%E7%B4%A2%E5%BC%95"><span class="nav-number">2.3.</span> <span class="nav-text">合理建立联合索引</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E7%B4%A2%E5%BC%95%E7%9A%84%E5%88%9B%E5%BB%BA%E3%80%81%E5%88%A0%E9%99%A4%E3%80%81%E4%BD%BF%E7%94%A8"><span class="nav-number">3.</span> <span class="nav-text">索引的创建、删除、使用</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E7%B4%A2%E5%BC%95%E7%9A%84%E5%88%9B%E5%BB%BA"><span class="nav-number">3.1.</span> <span class="nav-text">索引的创建</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%9F%A5%E7%9C%8B%E4%B8%8E%E5%88%A0%E9%99%A4%E7%B4%A2%E5%BC%95"><span class="nav-number">3.2.</span> <span class="nav-text">查看与删除索引</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E6%9C%89%E5%85%B3%E7%B4%A2%E5%BC%95%E7%9A%84%E8%A1%A5%E5%85%85%E4%BA%86%E8%A7%A3"><span class="nav-number">4.</span> <span class="nav-text">有关索引的补充了解</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E8%87%AA%E9%80%82%E5%BA%94%E5%93%88%E5%B8%8C%E7%B4%A2%E5%BC%95"><span class="nav-number">4.1.</span> <span class="nav-text">自适应哈希索引</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E7%B4%A2%E5%BC%95%E4%B8%8B%E6%8E%A8%EF%BC%88IPC%EF%BC%89"><span class="nav-number">4.2.</span> <span class="nav-text">索引下推（IPC）</span></a></li></ol></li></ol></div>
        </div>
        <!--/noindex-->

        <div class="site-overview-wrap sidebar-panel">
          <div class="site-author animated" itemprop="author" itemscope itemtype="http://schema.org/Person">
  <p class="site-author-name" itemprop="name">SongyangJi</p>
  <div class="site-description" itemprop="description"></div>
</div>
<div class="site-state-wrap animated">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
        <a href="/archives/">
          <span class="site-state-item-count">251</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
          <a href="/categories/">
        <span class="site-state-item-count">45</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
          <a href="/tags/">
        <span class="site-state-item-count">109</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>

        </div>
      </div>
    </div>

    
  </aside>


    </div>

    <div class="main-inner post posts-expand">


  


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="http://example.com/2021/10/27/MySQL%E7%B3%BB%E5%88%97%E2%80%94%E2%80%94%E7%B4%A2%E5%BC%95/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="SongyangJi">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="JsyBlog">
      <meta itemprop="description" content="">
    </span>

    <span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
      <meta itemprop="name" content="MySQL系列——索引 | JsyBlog">
      <meta itemprop="description" content="">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          MySQL系列——索引
        </h1>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>
      

      <time title="创建时间：2021-10-27 14:08:19 / 修改时间：16:30:02" itemprop="dateCreated datePublished" datetime="2021-10-27T14:08:19+08:00">2021-10-27</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/categories/MySQL/" itemprop="url" rel="index"><span itemprop="name">MySQL</span></a>
        </span>
    </span>

  
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">
        <h1 id="索引的数据结构"><a href="#索引的数据结构" class="headerlink" title="索引的数据结构"></a>索引的数据结构</h1><h2 id="B树、B-树"><a href="#B树、B-树" class="headerlink" title="B树、B+树"></a>B树、B+树</h2><blockquote>
<p>关于 B树、B+树本身的介绍，不是本文的重点，之后也许会专门写一篇文章详细介绍，</p>
</blockquote>
<p>这里直接就贴出wiki的链接了。</p>
<p><a target="_blank" rel="noopener" href="https://zh.wikipedia.org/wiki/B%E6%A0%91">B树</a></p>
<p><a target="_blank" rel="noopener" href="https://zh.wikipedia.org/wiki/B%2B%E6%A0%91">B+树</a></p>
<ul>
<li><p>为什么不使用普通的平衡二叉树（如RB树、AVL树）？</p>
<p>MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，<strong>磁盘IO 操作非常耗时</strong>，所以我们优化的重点就是尽量减少磁盘 IO 操作。<strong>访问二叉树的每个节点就会发生一次IO，如果想要减少磁盘IO操作，就需要尽量降低树的高度</strong>。那如何降低树的高度呢？</p>
<p>B树的特点就是一个多叉的平衡树，特点就是<strong>矮胖</strong>（相比于普通平衡二叉树的高瘦），在相同数据规模下访问节点的次数更少，换句话说，磁盘IO的开销更少。</p>
</li>
</ul>
<p>如下图，红色的是主键，黄色的其余的数据，蓝色的是指向子节点的指针。</p>
<p><img src="/2021/10/27/MySQL%E7%B3%BB%E5%88%97%E2%80%94%E2%80%94%E7%B4%A2%E5%BC%95/btree.png" alt="B树"></p>
<ul>
<li><p>为什么Innodb引擎使用B+树而不是B树？</p>
<ol>
<li><p>B树不支持范围查询的快速查找，你想想这么一个情况如果我们想要查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。</p>
</li>
<li><p>如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，<strong>一个页中可存储的数据量就会变少，树相应就会变高</strong>，磁盘IO次数就会变大。</p>
</li>
</ol>
<p>B+树和B树最主要的区别在于<strong>非叶子节点是否存储数据</strong>的问题</p>
<ul>
<li>B树：非叶子节点和叶子节点都会存储数据。</li>
<li>B+树：<strong>只有叶子节点才会存储数据，非叶子节点至存储键值</strong>。叶子节点之间使用双向指针连接，<strong>最底层的叶子节点形成了一个双向有序链表</strong>。</li>
</ul>
<p>当然，非要讲B+树和B树的缺点，就是每一次查询行数据，都一定要查询到叶子节点，不过这个缺点和它的优势相比可以忽略了。</p>
</li>
</ul>
<p><img src="/2021/10/27/MySQL%E7%B3%BB%E5%88%97%E2%80%94%E2%80%94%E7%B4%A2%E5%BC%95/b+tree.png" alt="B+树"></p>
<h2 id="MyISAM引擎中的索引"><a href="#MyISAM引擎中的索引" class="headerlink" title="MyISAM引擎中的索引"></a>MyISAM引擎中的索引</h2><p>MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，<strong>叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址</strong>。</p>
<p><strong>在 MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别</strong>。叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。</p>
<p><img src="/2021/10/27/MySQL%E7%B3%BB%E5%88%97%E2%80%94%E2%80%94%E7%B4%A2%E5%BC%95/myisam.png" alt="MyISAM主键索引"></p>
<p>MyISAM通过key_buffer<strong>把索引先缓存到内存中</strong>，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后<strong>通过索引找到磁盘相应数据</strong>。</p>
<p>这也就是为什么索引不在key buffer命中时，速度慢的原因。</p>
<h2 id="InnoDB引擎中的索引"><a href="#InnoDB引擎中的索引" class="headerlink" title="InnoDB引擎中的索引"></a>InnoDB引擎中的索引</h2><blockquote>
<p>首先需要澄清的一点是，MySQL 跟 B+ 树没有直接的关系，<strong>真正与 B+ 树有关系的是 MySQL 的默认存储引擎 InnoDB</strong>，MySQL 中存储引擎的主要作用是负责数据的存储和提取，除了 InnoDB 之外，MySQL 中也支持 MyISAM 作为表的底层存储引擎。</p>
<p><del>不过本文不介绍xianMyISAM，只介绍 InnoDB，会在介绍MySQL中的存储引擎文章中介绍 MyISAM引擎以及其索引特点。</del></p>
</blockquote>
<h3 id="主键索引（聚簇索引）"><a href="#主键索引（聚簇索引）" class="headerlink" title="主键索引（聚簇索引）"></a>主键索引（聚簇索引）</h3><p>通俗点讲：</p>
<p>聚簇索引的意思就是：将数据存储与索引放到了一块，找到索引也就找到了数据。</p>
<p>每个InnoDB表都有一个聚簇索引 ，<strong>聚簇索引使用B+树构建</strong>，<strong>叶子节点存储的数据是整行记录</strong>。一般情况下，<strong>聚簇索引基本上等同于主键索引</strong>，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。</p>
<p>InnoDB创建索引的具体规则如下：</p>
<ol>
<li>在表上定义主键PRIMARY KEY，InnoDB将<strong>主键</strong>用作聚簇索引。</li>
<li>如果表没有定义主键，InnoDB会选择<strong>第一个不为NULL的唯一索引列</strong>用作聚簇索引。</li>
<li>如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 <strong>ROWID字段</strong>构建聚簇索引。该ROWID字段会在插入新行时自动递增。</li>
</ol>
<p> 在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。</p>
<p><img src="/2021/10/27/MySQL%E7%B3%BB%E5%88%97%E2%80%94%E2%80%94%E7%B4%A2%E5%BC%95/innodb1.png" alt="InnoDB主键索引"></p>
<h3 id="二级索引（辅助索引）"><a href="#二级索引（辅助索引）" class="headerlink" title="二级索引（辅助索引）"></a>二级索引（辅助索引）</h3><p>除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，<strong>辅助索引中的叶子节点存储的数据是该行的主键值</strong>。</p>
<p><strong>辅助索引也使用B+树构建</strong>。</p>
<p><img src="/2021/10/27/MySQL%E7%B3%BB%E5%88%97%E2%80%94%E2%80%94%E7%B4%A2%E5%BC%95/innodb2.png" alt="InnoDB辅助索引"></p>
<p>如何使用二级索引来查询行数据呢？</p>
<p>第一步，在二级索引里根据索引列查询到主键值。</p>
<p>第二步，根据主键值再到聚簇索引里查询数据行。</p>
<p>这个操作就叫做<strong>回表</strong>。</p>
<p><img src="/2021/10/27/MySQL%E7%B3%BB%E5%88%97%E2%80%94%E2%80%94%E7%B4%A2%E5%BC%95/rolltable.png" alt="回表"></p>
<h2 id="聚簇索引-vs-非聚簇索引"><a href="#聚簇索引-vs-非聚簇索引" class="headerlink" title="聚簇索引 vs 非聚簇索引"></a>聚簇索引 vs 非聚簇索引</h2><h3 id="优势"><a href="#优势" class="headerlink" title="优势"></a>优势</h3><ul>
<li>由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。</li>
<li>辅助索引使用主键作为”指针”而不是使用地址值作为指针的好处是，<strong>减少了当出现行移动或者数据页分裂时辅助索引的维护工作</strong>。也就是聚簇索引这棵树由于页分裂导致的树的结构的调整，不会牵扯到辅助索引这个树的调整。</li>
<li><strong>可以把相关数据保存在一起</strong>。例如实现电子邮箱时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘 I&#x2F;O。</li>
</ul>
<h3 id="劣势"><a href="#劣势" class="headerlink" title="劣势"></a>劣势</h3><ol>
<li><p><strong>维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候</strong>。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片。</p>
</li>
<li><p>如果主键比较大的话，那辅助索引将会变的更大，因为<strong>辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间</strong>。</p>
</li>
</ol>
<h1 id="使用索引的一些注意点"><a href="#使用索引的一些注意点" class="headerlink" title="使用索引的一些注意点"></a>使用索引的一些注意点</h1><h2 id="最左匹配原则"><a href="#最左匹配原则" class="headerlink" title="最左匹配原则"></a>最左匹配原则</h2><p><strong>组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(&gt;、&lt;、between、like)就停止匹配。</strong></p>
<p>最左前缀匹配原则和联合索引的<strong>索引存储结构和检索方式</strong>是有关系的。</p>
<p>说白了，就是B树的节点的键进行比较的时候比较原则。</p>
<p>比方说，创建索引了<code>index_abc(a,b,c)</code>，</p>
<p>在组合索引树中，最底层的叶子节点按照第一列按a字段从左到右递增排列，但是b列和c列是相对无序的——只有在a相同的时候，b才是有序的。同理，c列只能在a，b两列相等的情况下小范围内递增有序。</p>
<h2 id="覆盖索引"><a href="#覆盖索引" class="headerlink" title="覆盖索引"></a>覆盖索引</h2><p>覆盖索引并不是说是索引结构，<strong>覆盖索引是一种很常用的优化手段</strong>。</p>
<p>因为在使用辅助索引的时候，我们只可以拿<strong>到主键值和建立的辅助索引列</strong>，相当于获取数据还需要再根据主键查询主键索引再获取到数据。但是试想下这么一种情况，，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而<strong>不需要回表</strong>。这种情况就是覆盖索引。</p>
<p>如何知道是否使用了覆盖索引呢？</p>
<p>explain执行计划，查询Extra列，如果是Using index，表明确实使用了覆盖索引，或者说没有回表操作。</p>
<h2 id="合理建立联合索引"><a href="#合理建立联合索引" class="headerlink" title="合理建立联合索引"></a>合理建立联合索引</h2><p>联合索引，在建立索引的时候，尽量在多个单列索引上判断下是否可以使用联合索引。联合索引的使用<strong>不仅可以节省空间</strong>，还可以更容易的使用到索引覆盖。</p>
<p>比如联合索引（a_b_c），是不是等于有了索引：a，a_b，a_b_c三个索引，这样是不是节省了空间，当然节省的空间并不是三倍于（a，a_b，a_b_c）三个索引。</p>
<p>总之：</p>
<ol>
<li>考虑当前是否已经存在多个可以合并的单列索引，如果有，那么<strong>将当前多个单列索引创建为一个联合索引</strong>。</li>
<li>当前索引<strong>存在频繁使用作为返回字段的列</strong>，这个时候就可以考虑当前列是否可以加入到当前已经存在索引上，使其查询语句可以使用到覆盖索引。</li>
</ol>
<h1 id="索引的创建、删除、使用"><a href="#索引的创建、删除、使用" class="headerlink" title="索引的创建、删除、使用"></a>索引的创建、删除、使用</h1><h2 id="索引的创建"><a href="#索引的创建" class="headerlink" title="索引的创建"></a>索引的创建</h2><ul>
<li><p>建表时创建</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> 表名（</span><br><span class="line">......</span><br><span class="line">[<span class="keyword">UNIQUE</span><span class="operator">|</span>FULLTEXT<span class="operator">|</span>SPATIAL] INDEX<span class="operator">|</span>KEY </span><br><span class="line">[索引名](字段名 [长度] [<span class="keyword">ASC</span> <span class="operator">|</span> <span class="keyword">DESC</span>]...) [<span class="keyword">USING</span> &#123;BTREE<span class="operator">|</span>HASH&#125;]</span><br><span class="line">）</span><br></pre></td></tr></table></figure>



<ul>
<li>UNIQUE: 可选。表示索引为唯一性索引；FULLTEXT:可选。表示索引为全文索引；SPATIAL:可选。表示索引为空间索引。</li>
<li>INDEX和KEY: 用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。</li>
<li>索引名: 可选。给创建的索引取一个新名称。</li>
<li>字段名: 指定索引对应的字段的名称，该字段必须是前面定义好的字段。</li>
<li>长度: 可选。指索引的长度，必须是字符串类型才可以使用。</li>
<li>ASC: 可选。表示升序排列、DESC:可选。表示降序排列。<br>注：索引方法默认使用B+TREE。</li>
</ul>
</li>
<li><p>建表后创建</p>
<p>第一种方法：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名] (字段名[(长度)] [ASC|DESC]) [USING 索引方法]；</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> mytable <span class="keyword">ADD</span> INDEX idx_c1c2c3 (mycol1, mycol2, mycol3);</span><br></pre></td></tr></table></figure>



<p>第二种方法:</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名(字段名) [USING 索引方法]；</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> INDEX idx_c1c2c3 <span class="keyword">ON</span> mytable(mycol1, mycol2, mycol3);</span><br></pre></td></tr></table></figure></li>
</ul>
<h2 id="查看与删除索引"><a href="#查看与删除索引" class="headerlink" title="查看与删除索引"></a>查看与删除索引</h2><ul>
<li>查看<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> index <span class="keyword">from</span> 表名;</span><br></pre></td></tr></table></figure></li>
<li>删除<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> INDEX 索引名 <span class="keyword">ON</span> 表名</span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> 表名 <span class="keyword">DROP</span> INDEX 索引名</span><br></pre></td></tr></table></figure></li>
</ul>
<h1 id="有关索引的补充了解"><a href="#有关索引的补充了解" class="headerlink" title="有关索引的补充了解"></a>有关索引的补充了解</h1><h2 id="自适应哈希索引"><a href="#自适应哈希索引" class="headerlink" title="自适应哈希索引"></a>自适应哈希索引</h2><p><strong>自适应hash索引（Adaptive Hash Index）</strong><br>是InnoDB存储引擎中的内存结构的组成部分。</p>
<p><strong>InnoDB存储引擎会自动根据访问的频率和模式来自动的为某些热点数据建立hash索引</strong>。</p>
<p>InnoDB存储引擎会监控对表上各索引页的查询，如果观察到建立hash索引可以提高查询速度，则自动建立hash索引。这就是自适应哈希索引（Adaptive Hash Index，AHI）<br><strong>AHI是通过缓存池的B+树页构造而来</strong>，因此建立的速度很快，而且不需要对整张表构建hash索引。</p>
<h2 id="索引下推（IPC）"><a href="#索引下推（IPC）" class="headerlink" title="索引下推（IPC）"></a>索引下推（IPC）</h2><p>索引条件下推 (ICP) 是 MySQL 5.6.2 里程碑版本中的新优化器功能之一。</p>
<p><strong>Index Condition Pushdown 的目标是将尽可能多的条件处理（主要是 WHERE 子句）从服务器层转移到存储引擎层</strong>。</p>
<p>举个具体的例子吧，</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> person (</span><br><span class="line">  personid <span class="type">INTEGER</span> <span class="keyword">PRIMARY</span> KEY,</span><br><span class="line">  firstname <span class="type">CHAR</span>(<span class="number">20</span>),</span><br><span class="line">  lastname <span class="type">CHAR</span>(<span class="number">20</span>),</span><br><span class="line">  postalcode <span class="type">INTEGER</span>,</span><br><span class="line">  age <span class="type">INTEGER</span>,</span><br><span class="line">  address <span class="type">CHAR</span>(<span class="number">50</span>),</span><br><span class="line">  KEY k1（postalcode，age）</span><br><span class="line"> ）ENGINE<span class="operator">=</span>InnoDB;</span><br></pre></td></tr></table></figure>



<p>如果有下面这条sql语句</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> lastname, firstname <span class="keyword">FROM</span> person</span><br><span class="line"><span class="keyword">WHERE</span> postalcode <span class="keyword">BETWEEN</span> <span class="number">5000</span> <span class="keyword">AND</span> <span class="number">5500</span> <span class="keyword">AND</span> age <span class="keyword">BETWEEN</span> <span class="number">21</span> <span class="keyword">AND</span> <span class="number">22</span>;</span><br></pre></td></tr></table></figure>

<p>根据最左匹配原则，只能使用到索引k1的postalcode列，age列无法使用（因为postalcode是一个范围查询）</p>
<p>如果，没有IPC, 那么怎么处理这条sql呢？</p>
<p>服务器层将sql推给存储引擎层，引擎层只能使用索引的第一个列查询一部分数据（比如说由10000条），</p>
<p>然后将这10000条数据返回给存储引擎层，然后服务器层再根据第二个条件进行过滤，最终留下1000条数据。</p>
<p>有了IPC之后呢，服务器层可以直接把where条件下推给存储引擎层，然后存储引擎可以直接查出这1000条数据，然后再返回给服务器层。</p>
<blockquote>
<p>参考链接</p>
</blockquote>
<p><a target="_blank" rel="noopener" href="http://olavsandstaa.blogspot.com/2011/04/mysql-56-index-condition-pushdown.html">MySQL 5.6：索引条件下推</a></p>
<blockquote>
<p>参考链接</p>
<p><a target="_blank" rel="noopener" href="https://blog.csdn.net/qq_35190492/article/details/109257302">一文搞懂MySQL索引所有知识点（建议收藏）</a></p>
<p><a target="_blank" rel="noopener" href="https://draveness.me/whys-the-design-mysql-b-plus-tree/">为什么 MySQL 使用 B+ 树</a></p>
<p><a target="_blank" rel="noopener" href="https://www.jianshu.com/p/fa8192853184">聚簇索引与非聚簇索引</a></p>
</blockquote>

    </div>

    
    
    

    <footer class="post-footer">
          <div class="post-tags">
              <a href="/tags/MySQL/" rel="tag"># MySQL</a>
              <a href="/tags/%E7%B4%A2%E5%BC%95/" rel="tag"># 索引</a>
          </div>

        

          <div class="post-nav">
            <div class="post-nav-item">
                <a href="/2021/10/25/SpringBoot%E6%95%B4%E5%90%88Kafka/" rel="prev" title="SpringBoot整合Kafka收发消息">
                  <i class="fa fa-chevron-left"></i> SpringBoot整合Kafka收发消息
                </a>
            </div>
            <div class="post-nav-item">
                <a href="/2021/10/27/MySQL%E7%B3%BB%E5%88%97%E2%80%94%E2%80%94%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E/" rel="next" title="MySQL系列——存储引擎">
                  MySQL系列——存储引擎 <i class="fa fa-chevron-right"></i>
                </a>
            </div>
          </div>
    </footer>
  </article>
</div>






</div>
  </main>

  <footer class="footer">
    <div class="footer-inner">


<div class="copyright">
  &copy; 
  <span itemprop="copyrightYear">2023</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">SongyangJi</span>
</div>
  <div class="powered-by">由 <a href="https://hexo.io/" rel="noopener" target="_blank">Hexo</a> & <a href="https://theme-next.js.org/muse/" rel="noopener" target="_blank">NexT.Muse</a> 强力驱动
  </div>

    </div>
  </footer>

  
  <div class="toggle sidebar-toggle" role="button">
    <span class="toggle-line"></span>
    <span class="toggle-line"></span>
    <span class="toggle-line"></span>
  </div>
  <div class="sidebar-dimmer"></div>
  <div class="back-to-top" role="button" aria-label="返回顶部">
    <i class="fa fa-arrow-up fa-lg"></i>
    <span>0%</span>
  </div>

<noscript>
  <div class="noscript-warning">Theme NexT works best with JavaScript enabled</div>
</noscript>


  
  <script src="https://cdnjs.cloudflare.com/ajax/libs/animejs/3.2.1/anime.min.js" integrity="sha256-XL2inqUJaslATFnHdJOi9GfQ60on8Wx1C2H8DYiN1xY=" crossorigin="anonymous"></script>
<script src="/js/comments.js"></script><script src="/js/utils.js"></script><script src="/js/motion.js"></script><script src="/js/schemes/muse.js"></script><script src="/js/next-boot.js"></script>

  <script src="https://cdnjs.cloudflare.com/ajax/libs/hexo-generator-searchdb/1.4.1/search.js" integrity="sha256-1kfA5uHPf65M5cphT2dvymhkuyHPQp5A53EGZOnOLmc=" crossorigin="anonymous"></script>
<script src="/js/third-party/search/local-search.js"></script>





  





</body>
</html>
